﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using EntityModels;
using System.Transactions;
using System.Data.EntityClient;

namespace DataAccessLayer
{
    public class DataAccess
    {
        private static DataAccess instance;
        private string connectionstring;
        public static DataAccess Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DataAccess();
                }
                return instance;
            }
        }
        public DataAccess() {

            string providerName = "System.Data.SqlClient";
            //string serverName = "ASPIRE5735Z\\SQLEXPRESS";
            string serverName = "localhost";
            string databaseName = "EventDotNet";
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = serverName;
            sqlBuilder.InitialCatalog = databaseName;
            sqlBuilder.IntegratedSecurity = true;
            string providerString = sqlBuilder.ToString();
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = providerName;
            entityBuilder.ProviderConnectionString = providerString;
            entityBuilder.Metadata = @"res://*/EventDotNetEntities.csdl|
                            res://*/EventDotNetEntities.ssdl|
                            res://*/EventDotNetEntities.msl";
            connectionstring = entityBuilder.ToString();
        }


        #region Users
        //Check if a user exist using the username return true or false 
        public bool UserExists(User user)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.User.Single(u => u.username == user.username);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        //Try to add an user, if all is ok return true else throw an exception
        public bool AddUser(User user)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {

                    context.User.AddObject(user);
                    context.SaveChanges();

                    return true;

                }
                catch
                {
                    throw new Exception("User not added");
                }
            }
        }

        //Try to login an user checking if it exists and if the username/passwords match, if all is ok return true else throw an exception
        public bool LogIn(User user)
        {
            if (!UserExists(user))
            {
                throw new Exception("User does not exist in the database");
            }
            else
            {
                using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
                {
                    try
                    {
                        context.User.Single(u => u.username == user.username && u.password == user.password);
                        return true;
                    }
                    catch
                    {
                        throw new Exception("Passwords does not match");
                    }
                }
            }
        }

        //Try to Update a User, we recibe a New User Entity but because we open in a diferent context we need to map again, reload the data to the maped User (aux) and then save it
        //If all is ok return true else throw an exception
        public bool UpdateUser(User user)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    User aux = context.User.Single(u => u.idmember == user.idmember);
                    aux.username = user.username;
                    aux.password = user.password;
                    aux.about = user.about;
                    aux.email = user.email;
                    aux.firstname = user.firstname;
                    aux.lastname = user.lastname;
                    aux.privateprofile = user.privateprofile;
                    aux.rating = user.rating;
                    aux.showattevents = user.showattevents;
                    aux.showemail = user.showemail;
                    aux.showmyevents = user.showmyevents;
                    aux.showname = user.showname;
                    context.SaveChanges();
                    return true;

                }
                catch
                {
                    throw new Exception("User NOT Updated");
                }
            }
        }

        //Try to get a user by its username, if all is ok return the user else throw an exception
        public User GetUser(String username)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    return context.User.Single(u => u.username == username);
                }
                catch
                {
                    throw new Exception("User NOT exist");
                }
            }

        }

        //Try to get a user by its id, if all is ok return the user else throw an exception
        public User GetUser(int id)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    return context.User.Single(u => u.idmember == id);
                }
                catch
                {
                    throw new Exception("User NOT exist");
                }
            }
        }

        //Try to get a list of Attending(flag=1) or Invited(flag=0) Users, if all is ok return the users list else throw an exception
        public List<User> GetUsers(Event ev, byte flag)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                List<User> res = new List<User>();
                bool type;
                try
                {
                    if (flag == 1) { type = true; } else { type = false; }
                    var query = from u in context.User
                                join i in context.Invites
                                        on u.idmember equals i.eidmember
                                where i.eidevent == ev.idevent && i.answer == type
                                select u;


                    foreach (User u in query)
                    {
                        res.Add(u);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Attending/Invited users");
                }
            }
        }

        #endregion

        #region Event


        public bool EventExists(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.Event.Single(e => e.idevent == ev.idevent);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool AddEvent(Event ev, List<Location> locations, List<DateTime> dates, List<TimeSpan> times)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                using (var transaction = new TransactionScope())
                {
                    try
                    {
                        context.Event.AddObject(ev);
                        context.SaveChanges();
                        foreach (Location l in locations)
                        {
                            context.Location.AddObject(l);
                            context.SaveChanges();
                            context.LocationOptions.AddObject(new LocationOptions(ev, l));
                            context.SaveChanges();
                        }
                        foreach (DateTime d in dates)
                        {
                            context.DateOptions.AddObject(new DateOptions(ev, d));
                            context.SaveChanges();
                        }
                        foreach (TimeSpan t in times)
                        {
                            context.TimeOptions.AddObject(new TimeOptions(ev, t));
                            context.SaveChanges();
                        }
                        Invites inv = new Invites();
                        inv.eidmember = ev.eidmember;
                        inv.eidevent = ev.idevent;
                        inv.answer = true;
                        context.Invites.AddObject(inv);
                        context.SaveChanges();
                        transaction.Complete();
                        return true;
                    }
                    catch
                    {
                        transaction.Dispose();
                        throw new Exception("Event not added");
                    }
                }
            }
        }

        public bool UpdateEvent(Event ev, List<Location> locations, List<DateOptions> dates, List<TimeOptions> times)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    Event evt = context.Event.Single(e => e.idevent == ev.idevent);
                    evt.name = ev.name;

                    var queryLoc = from l in context.Location
                                join lo in context.LocationOptions
                                       on l.idlocation equals lo.eidlocation
                                where lo.eidevent == ev.idevent
                                select l;

                    int i = 0;
                    foreach (Location l in queryLoc)
                    {
                        l.locationplace = locations[i].locationplace;
                        l.country = locations[i].country;
                        l.city = locations[i].city;
                        l.address = locations[i].address;
                        i++;
                    }

                    var queryDate = from d in context.DateOptions
                                where d.eidevent == ev.idevent
                                select d;
                    i = 0;
                    foreach (DateOptions d in queryDate)
                    {
                        d.DATE = dates[i].DATE;
                        i++;
                    }

                    var queryTime = from t in context.TimeOptions
                                where t.eidevent == ev.idevent
                                select t;

                    i = 0;
                    foreach (TimeOptions t in queryTime)
                    {
                        t.TIME = times[i].TIME;
                        i++;
                    }

                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public List<TimeOptions> getTimes(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                List<TimeOptions> res = new List<TimeOptions>();
                try
                {
                    var query = from t in context.TimeOptions
                                where t.eidevent == ev.idevent
                                select t;

                    foreach (TimeOptions t in query)
                    {
                        res.Add(t);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Times");
                }
            }

        }

        public List<DateOptions> getDates(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                List<DateOptions> res = new List<DateOptions>();
                try
                {
                    var query = from d in context.DateOptions
                                where d.eidevent == ev.idevent
                                select d;

                    foreach (DateOptions d in query)
                    {
                        res.Add(d);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Dates");
                }
            }
        }

        public List<Location> getLocations(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                List<Location> res = new List<Location>();
                try
                {
                    var query = from l in context.Location
                                join lo in context.LocationOptions
                                        on l.idlocation equals lo.eidlocation
                                where lo.eidevent == ev.idevent
                                select l;

                    foreach (Location l in query)
                    {
                        res.Add(l);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Dates");
                }
            }
        }

        public User getAdmin(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    User admin = context.User.Single(e => e.idmember == ev.eidmember);
                    return admin;
                }
                catch
                {
                    throw new Exception("Fail getting the Admin of event " + ev.name);
                }
            }


        }

        public List<Event> GetEvents(User user, byte flag)
        {
            switch (flag)
            {
                case 1:
                    return GetEventsByCreator(user);
                case 2:
                    return GetEventsByAttending(user);
                case 3:
                    return GetEventsByInvited(user);
                default:
                    throw new Exception("Fail Getting list of Events");

            }
        }

        public List<Event> GetEventsByCreator(User user)
        {
            List<Event> res = new List<Event>();
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    var query = from e in context.Event
                                where e.eidmember == user.idmember
                                select e;
                    foreach (Event e in query)
                    {
                        res.Add(e);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Events by Admin");
                }
            }
        }

        public List<Event> GetEventsByAttending(User user)
        {
            List<Event> res = new List<Event>();
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    var query = from e in context.Event
                                join i in context.Invites
                                on e.idevent equals i.eidevent
                                where i.eidmember == user.idmember && i.answer == true
                                select e;
                    foreach (Event e in query)
                    {
                        res.Add(e);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Events by Attending");
                }
            }
        }

        public List<Event> GetEventsByInvited(User user)
        {
            List<Event> res = new List<Event>();
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    var query = from e in context.Event
                                join i in context.Invites
                                on e.idevent equals i.eidevent
                                where i.eidmember == user.idmember && i.answer == false
                                select e;
                    foreach (Event e in query)
                    {
                        res.Add(e);
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail creating list of Events by Invited");
                }
            }
        }

        public Event GetEvent(int id)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    return context.Event.Single(e => e.idevent == id);
                }
                catch
                {
                    throw new Exception("Fail getting event with id: " + id);
                }
            }


        }

        public bool DeleteEvent(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                using (var transaction = new TransactionScope())
                {

                    List<Location> locations = getLocations(ev);
                    List<DateOptions> dates = getDates(ev);
                    List<TimeOptions> times = getTimes(ev);
                    var options = from lo in context.LocationOptions
                                  where lo.eidevent == ev.idevent
                                  select lo;
                    foreach (LocationOptions ops in options)
                    {
                        context.DeleteObject(ops);
                    }
                    foreach (Location l in locations)
                    {
                        context.Attach(l);
                        context.DeleteObject(l); ;
                    }
                    foreach (DateOptions d in dates)
                    {
                        context.Attach(d);
                        context.DeleteObject(d);
                    }
                    foreach (TimeOptions t in times)
                    {
                        context.Attach(t);
                        context.DeleteObject(t);
                    }
                    var invitations = from inv in context.Invites
                                      where inv.eidevent == ev.idevent
                                      select inv;
                    foreach (Invites i in invitations)
                    {
                        context.Attach(i);
                        context.DeleteObject(i);
                    }
                    var votes = from vot in context.Votes
                                where vot.eidevent == ev.idevent
                                select vot;
                    foreach (Votes v in votes)
                    {
                        context.Attach(v);
                        context.DeleteObject(v);
                    }

                    Event aux = context.Event.Single(e => e.idevent == ev.idevent);

                    context.Event.DeleteObject(aux);
                    context.SaveChanges();
                    transaction.Complete();
                    return true;
                }


            }
        }

        //Check if an user voted in a specific section for a event (type: 1=Location 2=Date 3=Time)
        public bool LocationVoted(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.Votes.Single(v => v.eidevent == ev.idevent && v.eidmember == us.idmember && v.TYPE == 1);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool DateVoted(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.Votes.Single(v => v.eidevent == ev.idevent && v.eidmember == us.idmember && v.TYPE == 2);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool TimeVoted(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.Votes.Single(v => v.eidevent == ev.idevent && v.eidmember == us.idmember && v.TYPE == 3);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        //Add one vote to the giving location and take note that this user alredy vote for locations in this event
        public bool VoteLocation(User us, Event ev, Location lo)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    LocationOptions option = context.LocationOptions.Single(op => op.eidevent == ev.idevent && op.eidlocation == lo.idlocation);
                    option.votes = option.votes + 1;
                    context.Votes.AddObject(new Votes(us, ev, 1));
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail Voting Location with id: " + lo.idlocation + "for event: " + ev.idevent);
                }
            }

        }

        public bool VoteDate(User us, Event ev, DateTime date)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    DateOptions option = context.DateOptions.Single(op => op.eidevent == ev.idevent && op.DATE == date);
                    option.votes = option.votes + 1;
                    context.Votes.AddObject(new Votes(us, ev, 2));
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail Voting Date: " + date + "for event: " + ev.idevent);
                }
            }
        }

        public bool VoteTime(User us, Event ev, TimeSpan time)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    TimeOptions option = context.TimeOptions.Single(op => op.eidevent == ev.idevent && op.TIME == time);
                    option.votes = option.votes + 1;
                    context.Votes.AddObject(new Votes(us, ev, 3));
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail Voting Time: " + time + "for event: " + ev.idevent);
                }
            }
        }

        public bool VotingFinished(Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    Event aux = context.Event.Single(e => e.idevent == ev.idevent);
                    if (aux.voteenddate < DateTime.Today)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch
                {
                    throw new Exception("Fail checking the end votation date");
                }
            }
        }

        public Location getLocationVoted(Event e)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    var vote = (from op in context.LocationOptions
                                where op.eidevent == e.idevent
                                select op.votes).Max();

                    var query = from l in context.Location
                                join op in context.LocationOptions
                                on l.idlocation equals op.eidlocation
                                where op.votes == vote
                                select l;

                    return (Location)query;

                }
                catch
                {
                    throw new Exception("Fail getting the final location");
                }
            }


        }

        public TimeSpan getTimeVoted(Event e)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    TimeSpan res = new TimeSpan();
                    var vote = (from op in context.TimeOptions
                                where op.eidevent == e.idevent
                                select op.votes).Max();

                    var query = from t in context.TimeOptions
                                where t.votes == vote
                                select t.TIME;
                    foreach (TimeSpan time in query)
                    {
                        res = time;
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail getting the final time");
                }
            }
        }

        public DateTime getDateVoted(Event e)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    DateTime res = new DateTime();
                    var vote = (from op in context.DateOptions
                                where op.eidevent == e.idevent
                                select op.votes).Max();

                    var query = from d in context.DateOptions
                                where d.votes == vote
                                select d.DATE;
                    foreach (DateTime date in query)
                    {
                        res = date;
                    }
                    return res;
                }
                catch
                {
                    throw new Exception("Fail getting the final date");
                }
            }


        }

        //Add a User to a invites table, return true if all is correct or throw a exception is something is wrong
        public bool Invite(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    context.Invites.AddObject(new Invites(us, ev));
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail inviting user: " + us.username + " to event: " + ev.name);
                }
            }

        }

        //Check if a user are attending to a event (return true) or not (return false)
        public bool Attending(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    Invites aux = context.Invites.Single(inv => inv.eidmember == us.idmember && inv.eidevent == ev.idevent);
                    return (bool)aux.answer;
                }
                catch
                {
                    return false;
                }
            }

        }

        //Updating the invitation of a user to attending
        public bool Attend(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    Invites aux = context.Invites.Single(inv => inv.eidmember == us.idmember && inv.eidevent == ev.idevent);
                    aux.answer = true;
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail inviting user: " + us.username + " to event: " + ev.name);
                }
            }
        }

        //Delete a user from a inviting list of a envent
        public bool NotAttend(User us, Event ev)
        {
            using (EventDotNetEntities context = new EventDotNetEntities(connectionstring))
            {
                try
                {
                    Invites aux = context.Invites.Single(inv => inv.eidmember == us.idmember && inv.eidevent == ev.idevent);
                    context.Invites.DeleteObject(aux);
                    context.SaveChanges();
                    return true;
                }
                catch
                {
                    throw new Exception("Fail deleting you from inviting list");
                }
            }
        }
        #endregion



    }
}

